package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Cmsmj;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;

@Repository("cmsmjDao")
public class CmsmjDaoImpl implements CmsmjDao {

	private Log log = LogFactory.getLog(getClass());
	
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Override
	public Cmsmj getCmsmjByMjaa(String mjaa) throws DatabaseException {
		String sql = "SELECT * FROM cmsmj WHERE mjaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{mjaa}, new BeanPropertyRowMapper<Cmsmj>(Cmsmj.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}



	@Override
	public PagesBean getCmsmjList(String mjaa, String mjab, int mjac, int mjae, int offset, int pageSize) throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Cmsmj> list = new ArrayList<Cmsmj>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT * FROM cmsmj ");
		if(mjaa != null && !"".equals(mjaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mjaa LIKE '%" + Function.formatSQLString(mjaa) + "%'");
		}
		if(mjab != null && !"".equals(mjab)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mjab LIKE '%" + Function.formatSQLString(mjab) + "%'");
		}
		if(mjac > 0) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mjac = " + mjac + "");
			
		}
		if(mjae > 0) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mjae = " + mjae + "");
		
		}
		sql.append(" ORDER BY mjac");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Cmsmj>(Cmsmj.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int saveCmsmj(Cmsmj cmsmj) throws DatabaseException {
		String sql = "INSERT INTO cmsmj (mjaa, mjab, mjac, mjad, mjae) VALUES (:mjaa, :mjab, :mjac, :mjad, :mjae)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmj);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmj(Cmsmj cmsmj) throws DatabaseException {
		String sql = "UPDATE cmsmj SET mjab=:mjab, mjac=:mjac, mjad=:mjad WHERE mjaa=:mjaa";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmj);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int deleteCmsmjByMjaa(String mjaa) throws DatabaseException {
		String sql = "DELETE FROM cmsmj WHERE mjaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{mjaa});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmj> getAllCmsmjList() throws DatabaseException {
		String sql = "SELECT * FROM cmsmj";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql.toString(), new BeanPropertyRowMapper<Cmsmj>(Cmsmj.class));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmjStatus(String mjaa, int mjae) throws DatabaseException {
		String sql = "UPDATE cmsmj SET mjae=" + mjae + " WHERE mjaa='" + mjaa + "'";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql);
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmj> getCmsmjListByMjac(int mjac) throws DatabaseException {
		String sql = "SELECT * FROM cmsmj WHERE mjac=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql.toString(), new Object[]{mjac}, new BeanPropertyRowMapper<Cmsmj>(Cmsmj.class));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

}
